<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
	<module name="entity">

		<statement name="delete" commandType="sp">
			<![CDATA[delete_entity]]>
			<parameter name="entityId" type="string" />
		</statement>

		<statement name="find_book_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			summary,
			publisher,
			author,
			publish_date as publishDate,
			files,
			s.`value` as depName
			from entity_book e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_book">
			<![CDATA[
			delete from entity_book where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="validate_exist_project_entity" resultType="bool" >
			<![CDATA[
			select exists(
			select project_id
			from project_entities
			where project_id = #project_id#
			and entity_id = #entity_id#);
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_project_entities">
			<![CDATA[
			insert into project_entities
			(
			project_id,
			entity_id,
			name,
			type,
			ref_project_id
			)
			select			
			#project_id#,
			#entity_id#,
			#name#,
			#type#,
			case #type#
			when 1 then (select e.project_id from  entity_software as e where e.`entity_id` = #entity_id#)                                        
            when 2 then (select e.project_id from  entity_service as e where e.`entity_id` = #entity_id#)           
            when 3 then (select e.project_id from  entity_system as e where e.`entity_id` = #entity_id#)
            when 4 then (select e.project_id from  entity_standard as e where e.`entity_id` = #entity_id#)
            when 5 then (select e.project_id from  entity_test as e where e.`entity_id` = #entity_id#)
			when 6 then (select e.project_id from  entity_paper as e where e.`entity_id` = #entity_id#)                                        
            when 7 then (select e.project_id from  entity_plan as e where e.`entity_id` = #entity_id#)           
            when 8 then (select e.project_id from  entity_book as e where e.`entity_id` = #entity_id#)
            when 9 then (select e.project_id from  entity_report as e where e.`entity_id` = #entity_id#)
            when 10 then (select e.project_id from  entity_technology_report as e where e.`entity_id` = #entity_id#)
			end 
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="type" type="string" />
		</statement>

		<statement name="delete_project_entities">
			<![CDATA[
			delete from  project_entities
			where project_id=#project_id#
			and entity_id=#entity_id#
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_book">
			<![CDATA[
			insert into entity_book
			(
			entity_id,
			project_id,
			name,
			summary,
			publisher,
			author,
			publish_date,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#summary#,
			#publisher#,
			#author#,
			#publish_date#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			name,
			type,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			8,
			#project_id#);			
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="publisher" type="string" />
			<parameter name="author" type="string" />
			<parameter name="publish_date" type="datetime" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_book">
			<![CDATA[
			update entity_book
			set entity_id=#entity_id#,
			name=#name#,
			summary=#summary#,
			publisher=#publisher#,
			author=#author#,
			publish_date=#publish_date#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set name=#name#
			where entity_id=#entity_id#
				]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="publisher" type="string" />
			<parameter name="author" type="string" />
			<parameter name="publish_date" type="datetime" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_paper_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			summary,
			journal_page as journalPage,
			journal_name as journalName,
			author,
			keyword,
			files,
			s.`value` as depName
			from entity_paper e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_paper">
			<![CDATA[
			delete from entity_paper where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_paper">
			<![CDATA[
			insert into entity_paper
			(
			entity_id,
			project_id,
			`name`,
			summary,
			journal_page,
			journal_name,
			author,
			keyword,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#summary#,
			#journal_page#,
			#journal_name#,
			#author#,
			#keyword#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			type,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			6,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="journal_page" type="string" />
			<parameter name="journal_name" type="string" />
			<parameter name="author" type="string" />
			<parameter name="keyword" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_paper">
			<![CDATA[
			update entity_paper
			set entity_id=#entity_id#,
			`name`=#name#,
			summary=#summary#,
			journal_page=#journal_page#,
			journal_name=#journal_name#,
			author=#author#,
			keyword=#keyword#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
				]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="journal_page" type="string" />
			<parameter name="journal_name" type="string" />
			<parameter name="author" type="string" />
			<parameter name="keyword" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_software_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			es.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			es.`name`,
			entity_summary as entitySummary,
			`case`,
			ifnull(sc2.`key`,'') as propertyType,
			sc2.`value` as propertyTypeValue,
			property_name as propertyName,
			auth_code as authCode,
			software_code as softwareCode,
			files,
			sourcecode,
			setupfile,
			sc1.`value` as depName
			from entity_software as es
			join project as p on es.project_id = p.project_id
			left join sys_code as sc1 on p.dep_id = sc1.key and sc1.type=40
            left join sys_code as sc2  on es.property_type = sc2.`key`  and sc2.type=34
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_software">
			<![CDATA[
			delete from entity_software where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
				]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_software">
			<![CDATA[
			insert into entity_software
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			property_type,
			property_name,
			auth_code,
			software_code,
			files,
			sourcecode,
			setupfile
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#property_type#,
			#property_name#,
			#auth_code#,
			#software_code#,
			#files#,
			#sourcecode#,
			#setupfile#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			1,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="update_software">
			<![CDATA[
			update entity_software
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			property_type=#property_type#,
			property_name=#property_name#,
			auth_code=#auth_code#,
			software_code=#software_code#,
			files=#files#,
			sourcecode=#sourcecode#,
			setupfile=#setupfile#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="find_system_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			es.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			es.`name`,
			entity_summary as entitySummary,
			`case`,
			ifnull(sc2.`key`,'') as propertyType,
            sc2.`value` as propertyTypeValue,
			property_name as propertyName,
			auth_code as authCode,
			software_code as softwareCode,
			files,
			sourcecode,
			setupfile,
			sc1.`value` as depName
			from entity_system as es
			join project as p on es.project_id = p.project_id
			left join sys_code as sc1 on p.dep_id = sc1.key and sc1.type=40
            left join sys_code as sc2  on es.property_type = sc2.`key`  and sc2.type=34
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_system">
			<![CDATA[
			delete from entity_system where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
				]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_system">
			<![CDATA[
			insert into entity_system
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			property_type,
			property_name,
			auth_code,
			software_code,
			files,
			sourcecode,
			setupfile
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#property_type#,
			#property_name#,
			#auth_code#,
			#software_code#,
			#files#,
			#sourcecode#,
			#setupfile#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			3,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="update_system">
			<![CDATA[
			update entity_system
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			property_type=#property_type#,
			property_name=#property_name#,
			auth_code=#auth_code#,
			software_code=#software_code#,
			files=#files#,
			sourcecode=#sourcecode#,
			setupfile=#setupfile#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="find_test_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			et.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			et.`name`,
			entity_summary as entitySummary,
			`case`,
			ifnull(sc2.`key`,'') as propertyType,
			sc2.`value` as propertyTypeValue,
			property_name as propertyName,
			auth_code as authCode,
			software_code as softwareCode,
			files,
			sourcecode,
			setupfile,
			sc1.`value` as depName
			from entity_test as et
			join project as p on et.project_id = p.project_id
			left join sys_code as sc1 on p.dep_id = sc1.key and sc1.type=40
            left join sys_code as sc2  on et.property_type = sc2.`key`  and sc2.type=34
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_test">
			<![CDATA[
			delete from entity_test where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_test">
			<![CDATA[
			insert into entity_test
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			property_type,
			property_name,
			auth_code,
			software_code,
			files,
			sourcecode,
			setupfile
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#property_type#,
			#property_name#,
			#auth_code#,
			#software_code#,
			#files#,
			#sourcecode#,
			#setupfile#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			5,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="update_test">
			<![CDATA[
			update entity_test
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			property_type=#property_type#,
			property_name=#property_name#,
			auth_code=#auth_code#,
			software_code=#software_code#,
			files=#files#,
			sourcecode=#sourcecode#,
			setupfile=#setupfile#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="property_type" type="string" />
			<parameter name="property_name" type="string" />
			<parameter name="auth_code" type="string" />
			<parameter name="software_code" type="string" />
			<parameter name="files" type="string" />
			<parameter name="sourcecode" type="string" />
			<parameter name="setupfile" type="string" />
		</statement>

		<statement name="find_plan_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			entity_summary as entitySummary,
			`case`,
			files,
			s.`value` as depName
			from entity_plan e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_plan">
			<![CDATA[
			delete from entity_plan where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_plan">
			<![CDATA[
			insert into entity_plan
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			7,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_plan">
			<![CDATA[
			update entity_plan
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_service_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			entity_summary as entitySummary,
			`case`,
			files,
			s.`value` as depName
			from entity_service e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_service">
			<![CDATA[
			delete from entity_service where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_service">
			<![CDATA[
			insert into entity_service
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			2,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_service">
			<![CDATA[
			update entity_service
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_standard_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			entity_summary as entitySummary,
			`case`,
			files,
			s.`value` as depName
			from entity_standard e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_standard">
			<![CDATA[
			delete from entity_standard where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;			
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
				]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_standard">
			<![CDATA[
			insert into entity_standard
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			4,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_standard">
			<![CDATA[
			update entity_standard
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_technology_report_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			entity_summary as entitySummary,
			`case`,
			files,
			s.`value` as depName
			from entity_technology_report e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_technology_report">
			<![CDATA[
			delete from entity_technology_report where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;
			delete from entity_auth_item where entity_id=#entity_id#;
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_technology_report">
			<![CDATA[
			insert into entity_technology_report
			(
			entity_id,
			project_id,
			`name`,
			entity_summary,
			`case`,
			files
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#entity_summary#,
			#case#,
			#files#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			10,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="update_technology_report">
			<![CDATA[
			update entity_technology_report
			set entity_id=#entity_id#,
			`name`=#name#,
			entity_summary=#entity_summary#,
			`case`=#case#,
			files=#files#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="entity_summary" type="string" />
			<parameter name="case" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_report_by_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			e.project_id as projectId,
			'' as refEntityId,
			'' as refEntityName,
			e.`name`,
			summary,
			summary_file as summaryFiles,
			content_file as contentFiles,
			s.`value` as depName
			from entity_report e
			join project p on e.project_id = p.project_id
			left join sys_code s on p.dep_id = s.key and s.type=40
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_report">
			<![CDATA[
			delete from entity_report where entity_id=#entity_id#;
			delete from entity_auth where entity_id=#entity_id#;			
			delete from project_entities where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_report">
			<![CDATA[
			insert into entity_report
			(
			entity_id,
			project_id,
			`name`,
			summary,
			summary_file,
			content_file
			)
			values
			(
			#entity_id#,
			#project_id#,
			#name#,
			#summary#,
			#summary_file#,
			#content_file#
			);
			insert into project_entities
			(
			project_id,
			entity_id,
			`name`,
			`type`,
			ref_project_id
			)
			values
			(
			#project_id#,
			#entity_id#,
			#name#,
			9,
			#project_id#);	
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="summary_file" type="string" />
			<parameter name="content_file" type="string" />
		</statement>

		<statement name="update_report">
			<![CDATA[
			update entity_report
			set entity_id=#entity_id#,
			`name`=#name#,
			summary=#summary#,
			summary_file = #summary_file#,
			content_file =#content_file#
			where entity_id=#entity_id#;

			update project_entities
			set `name`=#name#
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="summary_file" type="string" />
			<parameter name="content_file" type="string" />
		</statement>

		<statement name="find_report_item_by_id"  resultType="table">
			<![CDATA[
			select
			entity_report_item_id as entityReportItemId,
			entity_id as entityId,
			content,
			files
			from entity_report_item
			where entity_report_item_id=#entity_report_item_id#;
			]]>
			<parameter name="entity_report_item_id" type="string" />
		</statement>
		<statement name="find_report_item" allowPaging="true" resultType="table">
			<![CDATA[
			select
			entity_report_item_id as entityReportItemId,
			entity_id as entityId,
			content,
			files
			from entity_report_item
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>
		<statement name="find_report_item_count"  resultType="int">
			<![CDATA[
			select
			count(*)
			from entity_report_item
			where entity_id=#entity_id#;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_report_item">
			<![CDATA[
			delete from entity_report_item where entity_report_item_id=#entity_report_item_id#;
			]]>
			<parameter name="entity_report_item_id" type="string" />
		</statement>

		<statement name="create_report_item">
			<![CDATA[
			insert into entity_report_item
			(
			entity_report_item_id,
			entity_id,
			content,
			files
			)
			values
			(
			#entity_report_item_id#,
			#entity_id#,
			#content#,
			#files#
			);
			]]>
			<parameter name="entity_report_item_id" type="string" />
			<parameter name="entity_id" type="string" />
			<parameter name="content" type="string" />
			<parameter name="files" type="string" />
		</statement>

		<statement name="find_project_entity_auth_detail"  allowPaging="true" resultType="table">
			<![CDATA[
			select	
			projectName,
			entityId,
			entityName,	
			entityType,
			group_concat(distinct authDepIsConfirm) as depConfirms,
			group_concat(distinct authUserIsConfirm) as userConfirms,
			isOriginal,
			group_concat(distinct authUserName) as userNames
			from entityauthview	as eav	
			where projectStatus=4 
			and authDepId=#dep_id#
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="project_name" >
					<![CDATA[
					projectName like concat('%', #project_name#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[			
			group by entityId,entityName,entityType,projectName
			order by projectName,entityName
			]]>
			<parameter name="project_name" type="string"  matchType="contains"/>
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="find_project_entity_auth_detail_count"   resultType="int">
			<![CDATA[
			select	
			count(distinct entityId)
			from entityauthview	
			where projectStatus=4 
			and authDepId=#dep_id#
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					projectName like concat('%', #project_name#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="find_project_entity_auth"  allowPaging="true" resultType="table">
			<![CDATA[
			select	
			projectName,
			entityId,
			entityName,
			entityType,
			case entityType 
			isOriginal,
			group_concat(distinct authDepName) as depNames
			from entityauthview as eav
			where projectId=#project_id#
			group by entityId,entityName,entityType,projectName
			order by projectName,entityName
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_entity_auth_count" resultType="int">
			<![CDATA[
			select
			count(distinct entityId)
			from entityauthview
			where projectId=#project_id#			
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_entity_auth"  allowPaging="true" resultType="table">
			<![CDATA[
			select	
			projectName,
			group_concat(distinct authDepIsConfirm) as depConfirms,
			entityId,
			entityName,
			entityType,
			isOriginal,
			group_concat(distinct authDepName) as depNames
			from entityauthview as eav
			where projectStatus=4
			and isOriginal=1
			and projectDepId=#user_dep_id#			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					projectName like concat('%',#project_name#,'%')
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by entityId,entityName,entityType,projectName
			order by projectName,entityName
			]]>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="user_dep_id" type="string"/>
		</statement>

		<statement name="find_project_entity_auth_count" resultType="int">
			<![CDATA[
			select
			count(distinct entityId)
			from entityauthview
			where projectStatus=4
			and isOriginal=1
			and projectDepId=#user_dep_id#			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					projectName like concat('%',#project_name#,'%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="user_dep_id" type="string"/>
		</statement>

		<statement name="find_project_entity_auth_by_entity_id"  resultType="table">
			<![CDATA[
			select
			entityId,
			entityName,
			group_concat(distinct authUserIsConfirm) as userConfirms,
			group_concat(distinct authDepId) as depIds,
			group_concat(distinct authDepName) as depNames
			from entityauthview
			where projectStatus=4  
			and entityId=#entity_id#
			group by entityId,entityName;
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="find_project_entity_auth_item_by_entity_id"  resultType="table">
			<![CDATA[
			select
			entityId,
			entityName,
			group_concat(distinct authUserIsConfirm) as userConfirms,
			group_concat(distinct authUserId) as userIds,
			group_concat(distinct authUserName) as userNames
			from entityauthview
			where projectStatus=4 
			and entityId=#entity_id# 
			and authDepId=#dep_id#
			group by entityId,entityName;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="find_auth_by_id" allowPaging="true" resultType="table">
			<![CDATA[
			select
			ea.entity_id as entityId,
			su.user_id as userId,
			su.`name` as userName
			from entity_auth as ea
			join sec_user_ext as sue on ea.dep_id = sue.dep_id
			join sec_user as su on sue.user_id = su.user_id
			where ea.entity_id=#entity_id#
			order by userName
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="delete_auth">
			<![CDATA[
			delete from entity_auth where entity_id=#entity_id# and dep_id=#dep_id#;
			delete from entity_auth_item where entity_id=#entity_id# and dep_id=#dep_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="delete_auth">
			<![CDATA[
			delete from entity_auth where entity_id=#entity_id#; 		
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="create_auth_ok">
			<![CDATA[
			insert into entity_auth
			(
			entity_id,
			dep_id,
			is_confirm
			)
			values
			(
			#entity_id#,
			#dep_id#,
			#is_confirm#);
			insert into entity_auth_item(entity_id,user_id,dep_id,is_confirm)
			select #entity_id#,user_id,#dep_id#,1 from sec_user_ext where dep_id=#dep_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="is_confirm" type="int" />
		</statement>

		<statement name="create_auth_un_ok">
			<![CDATA[
			insert into entity_auth
			(
			entity_id,
			dep_id,
			is_confirm
			)
			values
			(
			#entity_id#,
			#dep_id#,
			#is_confirm#);			
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="is_confirm" type="int" />
		</statement>

		<statement name="create_auth_item">
			<![CDATA[
			insert into entity_auth_item
			(
			entity_id,
			user_id,
			dep_id,
			is_confirm
			)
			values
			(
			#entity_id#,
			#user_id#,
			#dep_id#,
			#is_confirm#);
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="is_confirm" type="int" />
		</statement>

		<statement name="delete_auth_item">
			<![CDATA[
			delete from entity_auth_item where entity_id=#entity_id# and dep_id=#dep_id#;
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>
		<statement name="change_entity_auth_item_ok">
			<![CDATA[
			 update entity_auth_item
			 set is_confirm=1
			 where entity_id=#entity_id#
			 and dep_id=#dep_id#			
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="change_entity_auth_ok">
			<![CDATA[
			 update entity_auth
			 set is_confirm=1
			 where entity_id in (
			 select entity_id from project_entities  as pe where pe.project_id=#project_id#)
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="change_entity_auth_ok_by_entity_id">
			<![CDATA[
			 update entity_auth
			 set is_confirm=1
			 where entity_id = #entity_id#
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="change_entity_auth_un_ok_by_entity_id">
			<![CDATA[
			 update entity_auth  
			 set is_confirm=0
			 where entity_id = #entity_id#
			]]>
			<parameter name="entity_id" type="string" />
		</statement>

		<statement name="change_entity_auth_item_un_ok">
			<![CDATA[
			 update entity_auth_item
			 set is_confirm=0
			 where entity_id = #entity_id#
			 and dep_id=#dep_id#
			]]>
			<parameter name="entity_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="find_entity" resultType="table" allowPaging="true" >
			<![CDATA[
			select
			projectType,
			projectName,
			entityName,
			entityId,
			entityType,
			entityTypeValue,
			depId,
			depName,
			(select exists(select entity_id from entity_auth where dep_id=#cur_dep_id#  and entity_id=p.entityId and is_confirm=1)) as IsDepAuthOk,
			(select exists(select user_id from entity_auth_item where dep_id=#cur_dep_id# and user_id =#user_id# and entity_id=p.entityId and is_confirm=1)) as IsAuthOk,
			(select  p.depId=#cur_dep_id# ) as IsDepOk
			from
			entitysumtemp as p 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					p.projectName like concat('%', #project_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="project_type">
					<![CDATA[
					p.projectType = #project_type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="entity_name">
					<![CDATA[
					p.entityName like concat('%', #entity_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					p.depId=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			order by entityName,projectName
			]]>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="project_type" type="string" />
			<parameter name="entity_name" type="string" matchType="contains"/>
			<parameter name="dep_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="cur_dep_id" type="string" />
		</statement>

		<statement name="find_entity_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from
			entitysumtemp
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					projectName like concat('%', #project_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="project_type">
					<![CDATA[
					projectType = #project_type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="entity_name">
					<![CDATA[
					entityName like concat('%', #entity_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					depId=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="project_type" type="string" />
			<parameter name="entity_name" type="string" matchType="contains"/>
			<parameter name="dep_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="cur_dep_id" type="string" />
		</statement>

		<statement name="find_ok_entities" resultType="table" allowPaging="true">
			<![CDATA[
			SELECT 
			distinct
			temp.projectName,
			temp.projectId,			
			temp.entityType,
			temp.entityName,
			temp.entityId,
			temp.projectDepName 
			FROM 
			(
			select 
			p.`name` as projectName,
			p.dep_id as projectDepId,
			p.project_id as projectId,
			pe.`type` as entityType,
			pe.`name` as entityName,
			pe.entity_id as entityId,
			sc.`value` as projectDepName,
			su.user_id as authUserId,
			p.`status` as projectStatus,
			ea.is_confirm as depAuthConfirm,
			sr.role_seq  as userRoleSeq,
			sue.dep_id as userDepId
			from 
			project_entities as pe
			join `project` `p` ON  `pe`.`ref_project_id` = `p`.`project_id` and pe.ref_project_id = pe.project_id
			left join `entity_auth` `ea` ON `ea`.`entity_id` = `pe`.`entity_id`  
			left join sec_user_ext as sue on sue.dep_id = ea.dep_id
			left join sec_user as su on sue.user_id = su.user_id
			left join sec_user_role as sur on sur.user_id=sue.user_id
			left join sec_role as sr on sur.role_id=sr.role_id 
			left join sys_code as sc on p.dep_id = sc.`key` and sc.`type`=40
			) as temp
			where temp.authUserId=#user_id#
			and temp.projectStatus=4
			and temp.projectId <> #project_id#
			and ((temp.projectDepId=temp.userDepId) or (temp.userRoleSeq='4' and temp.depAuthConfirm='1'))
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="entity_name">
					<![CDATA[
					temp.entityName like concat('%', #entity_name#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="user_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="entity_name" type="string" matchType="contains"/>
		</statement>

		<statement name="find_ok_entities_count" resultType="int">
			<![CDATA[
			SELECT 
			count(distinct temp.projectName,temp.projectId,temp.entityType,temp.entityName,	temp.entityId,temp.projectDepName)
			FROM 
			(
			select 
			p.`name` as projectName,
			p.dep_id as projectDepId,
			p.project_id as projectId,
			pe.`type` as entityType,
			pe.`name` as entityName,
			pe.entity_id as entityId,
			sc.`value` as projectDepName,
			su.user_id as authUserId,
			p.`status` as projectStatus,
			ea.is_confirm as depAuthConfirm,
			sr.role_seq  as userRoleSeq,
			sue.dep_id as userDepId
			from 
			project_entities as pe
			join `project` `p` ON  `pe`.`ref_project_id` = `p`.`project_id` and pe.ref_project_id = pe.project_id
			left join `entity_auth` `ea` ON `ea`.`entity_id` = `pe`.`entity_id`  
			left join sec_user_ext as sue on sue.dep_id = ea.dep_id
			left join sec_user as su on sue.user_id = su.user_id
			left join sec_user_role as sur on sur.user_id=sue.user_id
			left join sec_role as sr on sur.role_id=sr.role_id 
			left join sys_code as sc on p.dep_id = sc.`key` and sc.`type`=40
			) as temp
			where temp.authUserId=#user_id#
			and temp.projectStatus=4
			and temp.projectId <> #project_id#
			and ((temp.projectDepId=temp.userDepId) or (temp.userRoleSeq='4' and temp.depAuthConfirm='1'))
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="entity_name">
					<![CDATA[
					temp.entityName like concat('%', #entity_name#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="user_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="entity_name" type="string" matchType="contains"/>
		</statement>
		<statement name="find_project_info_by_entity_id" resultType="table">
			<![CDATA[
			select 
			distinct ref_project_id as projectId,
			name
			from project_entities
			where entity_id=#entity_id#
			limit 1;			
			]]>
			<parameter name="entity_id" type="string" />
		</statement>
	</module>
</root>
